package com.yutel.system.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.yutel.system.repository.BusinessTableBean;
import com.yutel.system.repository.ColumnsBean;
import com.yutel.system.service.IDmlService;

/**
 * 数据管理操作服务，有改表名及改列名的操作只能用于SQL-SERVER
 *
 * @author Mofy
 *
 */
public class DmlService extends JdbcDaoSupport implements IDmlService{

	private final String QUERY_TABLE_BY_KEYWORD = "SELECT TABLE_ID, TABLE_NAME, BUSINESS_NAME FROM XS_BUSINESS_TABLE WHERE SYSTEM_TABLE != '是' AND (BUSINESS_NAME LIKE ? OR TABLE_NAME LIKE ?)";
	private final String QUERY_TABLE_BY_ID = "SELECT TABLE_ID, TABLE_NAME, BUSINESS_NAME FROM XS_BUSINESS_TABLE WHERE TABLE_ID = ? ";
	private final String INSERT_TABLE = "INSERT INTO XS_BUSINESS_TABLE(TABLE_NAME, BUSINESS_NAME, SYSTEM_TABLE) VALUES(?, ?, '否') SELECT @@IDENTITY AS ID";
	private final String UPDATE_TABLE = "UPDATE XS_BUSINESS_TABLE SET TABLE_NAME = ?, BUSINESS_NAME = ? WHERE TABLE_ID = ?";
	private final String DELETE_TABLE = "DELETE FROM XS_BUSINESS_TABLE WHERE TABLE_ID = ? ";
	private final String QUERY_COLUMN_BY_TABLE_ID = "SELECT COL_ID, COL_NAME, BUSINESS_NAME, DATA_TYPE, NULL_ABLE, IF_PRIMARY FROM XS_BUSINESS_COL WHERE COL_NAME != 'ID' AND TABLE_ID = ? ";
	private final String INSERT_COLUMN = "INSERT INTO XS_BUSINESS_COL(TABLE_ID, COL_NAME, BUSINESS_NAME, DATA_TYPE, NULL_ABLE, IF_PRIMARY) VALUES(?, ?, ?, ?, ?, ?)";
	private final String UPDATE_COLUMN = "UPDATE XS_BUSINESS_COL SET COL_NAME = ?, BUSINESS_NAME = ?, DATA_TYPE = ?, NULL_ABLE = ? WHERE COL_ID = ?";
	private final String DELETE_COLUMN_BY_ID = "DELETE FROM XS_BUSINESS_COL WHERE COL_ID = ?";
	private final String DELETE_COLUMN_BY_TABLE_ID = "DELETE FROM XS_BUSINESS_COL WHERE TABLE_ID = ?";

	/* (non-Javadoc)
	 * @see com.yutel.system.service.IDmlService#queryTablesByKeyWord(java.lang.String)
	 */
	public List<BusinessTableBean> queryTablesByKeyWord(String keyWord)
	{
		if(keyWord == null)
		{
			keyWord = "";
		}
		keyWord = "%" + keyWord + "%";
		List<BusinessTableBean> tables = getJdbcTemplate().query(QUERY_TABLE_BY_KEYWORD, new Object[]{keyWord, keyWord}, new SqlServerBusinessTableBeanRowMaper());
		for (BusinessTableBean table : tables)
		{
			getJdbcTemplate().query(
					QUERY_COLUMN_BY_TABLE_ID,
					new Object[] { table.getTableId() },
					new SqlServerColumnsBeanResultSetExtractor(table));
		}
		return tables;
	}

	/* (non-Javadoc)
	 * @see com.yutel.system.service.IDmlService#saveTable(com.yutel.system.repository.BusinessTableBean)
	 */
	public void saveTable(BusinessTableBean table)
	{
		if(table.getTableId() == null)
		{
			createTable(table);
			createTableMetaData(table);
		}
		else
		{
			BusinessTableBean oldTable = queryTableById(table.getTableId());
			alterTable(oldTable, table);
			alterTableMetaData(oldTable, table);
		}
	}

	/* (non-Javadoc)
	 * @see com.yutel.system.service.IDmlService#dropTable(java.lang.String)
	 */
	public void dropTable(String tableId)
	{
		BusinessTableBean table = queryTableById(tableId);
		getJdbcTemplate().update("DROP TABLE " + table.getTableName());
		getJdbcTemplate().update(DELETE_TABLE, new Object[]{table.getTableId()});
		getJdbcTemplate().update(DELETE_COLUMN_BY_TABLE_ID, new Object[]{table.getTableId()});
	}

	/**
	 * 创建表
	 *
	 * @param table
	 */
	private void createTable(BusinessTableBean table)
	{
		String columns = "ID int identity PRIMARY KEY";
		for (ColumnsBean column : table.getColumns())
		{
			columns += ",\n" + columnDefinition(column);
		}
		String createSql = "CREATE TABLE " + table.getTableName() + "(" + columns + ")";
		getJdbcTemplate().execute(createSql);
	}

	/**
	 * 创建表信息
	 *
	 * @param table
	 */
	private void createTableMetaData(BusinessTableBean table)
	{
		String tableId = "" + getJdbcTemplate().queryForInt(INSERT_TABLE, new Object[]{table.getTableName(), table.getTableBusinessName()});
		createColumnMetaData(tableId, ColumnsBean.KEY_COLUMN);
		for (ColumnsBean column : table.getColumns()) {
			createColumnMetaData(tableId, column);
		}
	}

	/**
	 * 创建列信息
	 *
	 * @param tableId
	 * @param column
	 */
	private void createColumnMetaData(String tableId, ColumnsBean column)
	{
		getJdbcTemplate().update(INSERT_COLUMN, new Object[]{
				tableId,
				column.getFieldName(),
				column.getBusinName(),
				column.getFieldType(),
				column.getNullAble(),
				column.getPrimaryKey()
			});
	}

	/**
	 * 修改表定义
	 *
	 * @param newTable
	 */
	private void alterTable(BusinessTableBean oldTable, BusinessTableBean newTable)
	{
		//改表名
		if(!newTable.getTableName().equalsIgnoreCase(oldTable.getTableName()))
		{
			renameTable(oldTable, newTable);
		}
		//将新表中不存在的列删除
		for (ColumnsBean oldColumn : oldTable.getColumns())
		{
			boolean remain = false;
			for (ColumnsBean newColumn : newTable.getColumns())
			{
				if(oldColumn.getColumnId().equalsIgnoreCase(newColumn.getColumnId()))
				{
					remain = true;
					break;
				}
			}
			if(!remain)
			{
				dropColumn(newTable, oldColumn);
			}
		}
		//增加列，修改列，删除列
		for (ColumnsBean newColumn : newTable.getColumns())
		{
			if(newColumn.getColumnId() == null)
			{
				addColumn(newTable, newColumn);
				continue;
			}
			for (ColumnsBean oldColumn : oldTable.getColumns())
			{
				if(newColumn.getColumnId().equalsIgnoreCase(oldColumn.getColumnId()))
				{
					alterColumn(newTable, oldColumn, newColumn);
					break;
				}
			}
		}
	}

	/**
	 * 修改表信息
	 *
	 * @param oldTable
	 * @param newTable
	 */
	private void alterTableMetaData(BusinessTableBean oldTable, BusinessTableBean newTable)
	{
		//更改表信息
		if(! (newTable.getTableName().equalsIgnoreCase(oldTable.getTableName())
				&& newTable.getTableBusinessName().equals(oldTable.getTableBusinessName())))
		{
			getJdbcTemplate().update(UPDATE_TABLE, new Object[]{newTable.getTableName(), newTable.getTableBusinessName(), newTable.getTableId()});
		}

		//增加，修改列信息
		for (ColumnsBean newColumn : newTable.getColumns())
		{
			if(newColumn.primaryKey())
			{
				continue;
			}
			if(newColumn.getColumnId() == null)
			{
				createColumnMetaData(newTable.getTableId(), newColumn);
				continue;
			}
			for (ColumnsBean oldColumn : oldTable.getColumns())
			{
				if(newColumn.getColumnId().equalsIgnoreCase(oldColumn.getColumnId()))
				{
					if(!(newColumn.getFieldName().equalsIgnoreCase(oldColumn.getFieldName())
							&& newColumn.getBusinName().equals(oldColumn.getBusinName())
							&& newColumn.getFieldType().equals(oldColumn.getFieldType())
							&& newColumn.getNullAble().equals(oldColumn.getNullAble())))
					{
						getJdbcTemplate().update(UPDATE_COLUMN, new Object[]{
								newColumn.getFieldName(),
								newColumn.getBusinName(),
								newColumn.getFieldType(),
								newColumn.getNullAble(),
								newColumn.getColumnId()
							});
					}
					break;
				}
			}
		}
		//将新表中不存在的列信息删除
		for (ColumnsBean oldColumn : oldTable.getColumns())
		{
			if(oldColumn.primaryKey())
			{
				continue;
			}
			boolean remain = false;
			for (ColumnsBean newColumn : newTable.getColumns())
			{
				if(oldColumn.getColumnId().equalsIgnoreCase(newColumn.getColumnId()))
				{
					remain = true;
					break;
				}
			}
			if(!remain)
			{
				getJdbcTemplate().update(DELETE_COLUMN_BY_ID, new Object[]{oldColumn.getColumnId()});
			}
		}
	}

	/* (non-Javadoc)
	 * @see com.yutel.system.service.IDmlService#queryTableById(java.lang.String)
	 */
	public BusinessTableBean queryTableById(String tableId)
	{
		BusinessTableBean table = (BusinessTableBean) getJdbcTemplate().queryForObject(
				QUERY_TABLE_BY_ID,
				new Object[]{tableId},
				new SqlServerBusinessTableBeanRowMaper()
				);
		getJdbcTemplate().query(
				QUERY_COLUMN_BY_TABLE_ID,
				new Object[] { tableId },
				new SqlServerColumnsBeanResultSetExtractor(table));
		return table;
	}

	/**
	 * 重命名表
	 *
	 * @param oldTable
	 * @param newTable
	 */
	private void renameTable(BusinessTableBean oldTable, BusinessTableBean newTable)
	{
		getJdbcTemplate().execute("SP_RENAME '" + oldTable.getTableName() + "', '" + newTable.getTableName() + "', 'OBJECT'");
	}

	/**
	 * 增加列
	 *
	 * @param table
	 * @param column
	 */
	private void addColumn(BusinessTableBean table, ColumnsBean column)
	{
		getJdbcTemplate().execute( "ALTER TABLE " + table.getTableName() + " ADD " + columnDefinition(column));
	}

	/**
	 * 删除列
	 *
	 * @param table
	 * @param column
	 */
	private void dropColumn(BusinessTableBean table, ColumnsBean column)
	{
		getJdbcTemplate().execute(
				"ALTER TABLE " + table.getTableName()
				+ " DROP COLUMN "
				+ column.getFieldName());
	}

	/**
	 * 更改列
	 *
	 * @param table
	 * @param oldColumn
	 * @param newColumn
	 */
	private void alterColumn(BusinessTableBean table, ColumnsBean oldColumn, ColumnsBean newColumn)
	{
		if(!newColumn.getFieldName().equalsIgnoreCase(oldColumn.getFieldName()))
		{
			renameColumn(table, oldColumn, newColumn);
		}
		if(! (newColumn.getDbType().equalsIgnoreCase(oldColumn.getDbType())
				&& newColumn.getNullAble().equals(oldColumn.getNullAble())))
		{
			getJdbcTemplate().execute("ALTER TABLE " + table.getTableName() + " ALTER COLUMN "
					+ columnDefinition(newColumn));
		}
	}

	/**
	 * 重命名列
	 *
	 * @param table
	 * @param oldColumn
	 * @param newColumn
	 */
	private void renameColumn(BusinessTableBean table, ColumnsBean oldColumn, ColumnsBean newColumn)
	{
		getJdbcTemplate().execute("SP_RENAME '" + table.getTableName() + "." + oldColumn.getFieldName() + "','" + newColumn.getFieldName() + "','COLUMN'");
	}

	private String columnDefinition(ColumnsBean column)
	{
		return column.getFieldName() + " " + column.getDbType() + " " + (column.nullAble() ? "NULL" : "NOT NULL");
	}
}

class SqlServerBusinessTableBeanRowMaper implements RowMapper
{
	public Object mapRow(ResultSet rs, int rowIndex) throws SQLException {
		BusinessTableBean table = new BusinessTableBean();
		table.setTableId(rs.getString("TABLE_ID"));
		table.setTableName(rs.getString("TABLE_NAME"));
		table.setTableBusinessName(rs.getString("BUSINESS_NAME"));
		return table;
	}
}
class SqlServerColumnsBeanResultSetExtractor  implements ResultSetExtractor
{
	private BusinessTableBean table;

	public SqlServerColumnsBeanResultSetExtractor(BusinessTableBean table) {
		this.table = table;
	}

	public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
		while(rs.next())
		{
			ColumnsBean column = new ColumnsBean();
			column.setColumnId(rs.getString("COL_ID"));
			column.setFieldName(rs.getString("COL_NAME"));
			column.setBusinName(rs.getString("BUSINESS_NAME"));
			column.setFieldType(rs.getString("DATA_TYPE"));
			column.setNullAble(rs.getString("NULL_ABLE"));
			column.setPrimaryKey(rs.getString("IF_PRIMARY"));
			table.addColumn(column);
		}
		return table;
	}
}